package com.jsbb.util;

import com.jsbb.core.exception.ServiceException;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * POI Excel具类
 *
 * @author jsbb
 */
public class ExcelUtils {

    private static final String XLS = ".xls";
    private static final String XLSX = ".xlsx";
    private static final String FIREFOX = "firefox";

    /**
     * 导出Excel
     *
     * @param list
     * @param headers
     * @param columns
     * @param request
     * @param response
     * @param fileName
     * @param <T>
     */
    public static <T> void exportExcel(List<T> list, String[] headers, String[] columns, HttpServletRequest request, HttpServletResponse response, String fileName) {
        try {
            Workbook workbook = null;
            if (fileName.endsWith(XLS)) {
                workbook = new HSSFWorkbook();
            } else if (fileName.endsWith(XLSX)) {
                workbook = new XSSFWorkbook();
            } else {
                fileName += XLSX;
                workbook = new XSSFWorkbook();
            }
            Sheet sheet = workbook.createSheet();
            Row row = sheet.createRow(0);
            Cell cell;
            for (int i = 0; i < headers.length; i++) {
                cell = row.createCell(i);
                cell.setCellValue(headers[i]);
            }
            int index = 1;
            for (T t : list) {
                row = sheet.createRow(index);
                index++;
                for (short i = 0; i < columns.length; i++) {
                    cell = row.createCell(i);
                    String fieldName = columns[i];
                    Object value = null;
                    Map map = null;
                    if (t instanceof Map) {
                        map = (Map) t;
                        value = map.get(fieldName);
                    } else {
                        String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                        Class tCls = t.getClass();
                        Method getMethod = tCls.getMethod(getMethodName, new Class[]{});
                        value = getMethod.invoke(t, new Class[]{});
                    }
                    String textValue = null;
                    if (value == null) {
                        textValue = "";
                    } else if (value instanceof Date) {
                        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        textValue = formatter.format(value);
                    } else {
                        textValue = value.toString();
                    }
                    cell.setCellValue(textValue);
                }
            }
            String userAgent = request.getHeader("USER-AGENT").toLowerCase();
            if (userAgent.contains(FIREFOX)) {
                fileName = new String(fileName.getBytes(), "iso-8859-1");
            } else {
                fileName = URLEncoder.encode(fileName, "UTF8");
            }
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName);
            response.setHeader("Pragma", "No-cache");
            OutputStream ouputStream = response.getOutputStream();
            workbook.write(ouputStream);
            ouputStream.flush();
            ouputStream.close();
        } catch (Exception e) {
            throw new ServiceException("导出Excel异常:" + e.getMessage());
        }
    }

    /**
     * Excel转List
     *
     * @param multipartFile
     * @param clazz
     * @param columns
     * @param <T>
     * @return
     * @throws Exception
     */
    public static <T> List<T> importExcel(MultipartFile multipartFile, Class<T> clazz, String[] columns) throws Exception {
        List<T> result = new ArrayList<>();
        Workbook workbook = getWorkbook(multipartFile);
        try {
            for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
                Sheet sheet = workbook.getSheetAt(sheetNum);
                if (sheet == null) {
                    continue;
                }
                for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                    Row row = sheet.getRow(rowNum);
                    if (row != null) {
                        T object = clazz.getDeclaredConstructor().newInstance();
                        for (int cellNum = 0; cellNum < columns.length; cellNum++) {
                            Cell cell = row.getCell(cellNum);
                            String value = getCellValue(cell);
                            String fieldName = columns[cellNum];

                            Field[] fields = clazz.getDeclaredFields();
                            for (Field field : fields) {
                                String name = field.getName();
                                if (fieldName.equals(name)) {
                                    String setFieldName = "set" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                                    Method method = clazz.getMethod(setFieldName, new Class[]{field.getType()});
                                    if (field.getType() == String.class) {
                                        method.invoke(object, value);
                                    } else if (field.getType() == Integer.class) {
                                        method.invoke(object, Integer.valueOf(value));
                                    } else if (field.getType() == Date.class) {
                                        method.invoke(object, new Date());
                                    } else if (field.getType() == LocalDate.class) {
                                        method.invoke(object, LocalDate.parse(value, DateTimeFormatter.ofPattern("yyyy-MM-dd")));
                                    } else {
                                        LoggerUtils.info("未赋值={}", setFieldName);
                                    }
                                }
                            }
                        }
                        result.add(object);
                    }
                }
            }
            return result;
        } catch (Exception e) {
            LoggerUtils.error("异常", e);
            throw new ServiceException("解析Excel异常");
        }
    }

    public static Workbook getWorkbook(MultipartFile multipartFile) throws Exception {
        String fileName = multipartFile.getOriginalFilename();
        if (fileName == null || "".equals(fileName)) {
            throw new ServiceException("请上传文件");
        }
        Workbook workbook;
        if (fileName.endsWith(XLS)) {
            workbook = new HSSFWorkbook(multipartFile.getInputStream());
        } else if (fileName.endsWith(XLSX)) {
            workbook = new XSSFWorkbook(multipartFile.getInputStream());
        } else {
            throw new ServiceException("文件格式不支持");
        }
        return workbook;
    }

    public static String getCellValue(Cell cell) {
        switch (cell.getCellType()) {
            case NUMERIC:
                short format = cell.getCellStyle().getDataFormat();
                boolean flag = format == 14 || format == 31 || format == 57 || format == 58
                        || (176 <= format && format <= 178) || (182 <= format && format <= 196)
                        || (210 <= format && format <= 213) || (208 == format);
                if (flag) {
                    double value = cell.getNumericCellValue();
                    Date date = DateUtil.getJavaDate(value);
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    return sdf.format(date);
                } else {
                    return new BigDecimal(cell.getNumericCellValue()).stripTrailingZeros().toPlainString();
                }
            case STRING:
                return cell.getStringCellValue();
            case FORMULA:
                return cell.getCellFormula();
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            default:
                return "";
        }
    }
}
